package com.zyd.dao;

import com.zyd.io.model.SocketNowEntity;
import com.zyd.io.vo.vehicle.VehicleTrackResVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
public class SocketNowBlackCarDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 黑车轨迹表插入数据
     * @param socketNowEntity
     */
    public void save(SocketNowEntity socketNowEntity) {
        String sql="INSERT INTO socket_now_blackcar (id,event_type,base_id,base_version,tag_id,tag_version,power,bak_1,bak_2,bak_3,rec_time,forward_time,islock,locktime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        jdbcTemplate.update(sql,socketNowEntity.getId(),
                socketNowEntity.getEvent_type(),
                socketNowEntity.getBase_id(),
                socketNowEntity.getBase_version(),
                socketNowEntity.getTag_id(),
                socketNowEntity.getTag_version(),
                socketNowEntity.getPower(),
                socketNowEntity.getBak_1(),
                socketNowEntity.getBak_2(),
                socketNowEntity.getBak_3(),
                socketNowEntity.getRec_time(),
                socketNowEntity.getForward_time(),
                socketNowEntity.getIslock(),
                socketNowEntity.getLocktime()
        );
    }

    /**
     * 根据 tagid删除黑车轨迹表数据
     * @param tagid
     */
    public void deleteByTagId(String tagid) {
        String sql="DELETE FROM socket_now_blackcar WHERE tag_id = ? ";
        jdbcTemplate.update(sql, tagid);
    }

    /**
     * 连接查询黑车轨迹数据
     * @param tagcode
     * @param sTime
     * @param eTime
     * @return
     */
    public List<VehicleTrackResVO> findTrack(String tagcode, String sTime, String eTime) {
        String sql ="SELECT a.stationcode,a.jd,a.wd,a.address,a.stationname,b.tag_id,b.rec_time AS aftertime  FROM  zydzn_station a  LEFT JOIN socket_now_blackcar b  ON a.stationcode = b.base_id   WHERE  b.rec_time >= ?   AND b.rec_time <= ?  AND a.dr=0  AND  b.tag_id = ? ORDER BY b.rec_time ";
        String startime = null;
        String endtime = null;
        try {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            Date date = format1.parse(sTime);
            startime = dateFormat.format(date);
            Date date2 = format1.parse(eTime);
            endtime = dateFormat.format((date2.getTime()+60*1000)-1);
        } catch (ParseException e) {
            System.out.println("日期格式化异常");
            e.printStackTrace();
        }
        String vinno = null;
        try {
            //queryForObject如果没有查到数据会报异常  要处理
            vinno = jdbcTemplate.queryForObject("SELECT vinno FROM zydzn_vehicle WHERE tagcode = ? ", String.class, tagcode);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        String finalVinno = vinno;
        return jdbcTemplate.query(sql, (resultSet, i) -> {
            VehicleTrackResVO ret = new VehicleTrackResVO();
            ret.setStationcode(resultSet.getString("stationcode"));
            ret.setJd(resultSet.getString("jd"));
            ret.setWd(resultSet.getString("wd"));
            ret.setAddress(resultSet.getString("address"));
            ret.setStationname(resultSet.getString("stationname"));
            ret.setAftertime(resultSet.getString("aftertime"));
            String tag_id = resultSet.getString("tag_id");
            ret.setVinno(finalVinno);
            return ret;
        }, startime, endtime, tagcode);//预编译的sql参宿传入;
    }

    public List findTrackBy5Minute() {
        //300000
        Date time = new Date(System.currentTimeMillis() - 300000);
        return jdbcTemplate.queryForList("SELECT b.vinno ,t.*,s.address FROM zydzn_vehicle AS b RIGHT JOIN socket_now_blackcar AS t ON b.tagcode=t.tag_id LEFT JOIN zydzn_station AS s ON t.base_id=s.stationcode WHERE t.rec_time >= ? ORDER BY t.rec_time DESC " ,time);
    }
}
